CREATE PROCEDURE sp_asi_FlowdownNameIndex
     @co_id varchar(10),
     @fieldName varchar(61),
     @fieldValue varchar(8000) = NULL,
     @indexName varchar(31) = NULL
AS
if @indexName is null
begin
   select @indexName = (case when ParameterName = 'Member_Control.Index1Fld' then '1'
     when ParameterName = 'Member_Control.Index2Fld' then '2'
     when ParameterName = 'Member_Control.Index3Fld' then '3'
     when ParameterName = 'Member_Control.Index4Fld' then '4'
     end) from System_Params where ParameterName like 'Member_Control.Index-3Fld' and ShortValue = @fieldName
end
if @indexName is not null
   begin
     declare @isAddress int
     select @isAddress = (case
       when LEFT(@fieldName,12) = 'Name_Address' then 1
       when @fieldName = 'Name.CITY' then 1
       when @fieldName = 'Name.STATE_PROVINCE' then 1
       when @fieldName = 'Name.ZIP' then 1
       when @fieldName = 'Name.COUNTRY' then 1
       when @fieldName = 'Name.MAIL_CODE' then 1
       when @fieldName = 'Name.CRRT' then 1
       when @fieldName = 'Name.BAR_CODE' then 1
       when @fieldName = 'Name.COUNTY' then 1
       when @fieldName = 'Name.FULL_ADDRESS' then 1
       when @fieldName = 'Name.WORK_PHONE' then 1
       when @fieldName = 'Name.FAX' then 1
       when @fieldName = 'Name.TOLL_FREE' then 1
       else 0
     end)
     declare @childID varchar(10)
     if @isAddress = 1
     begin
       declare Get_Address_Children cursor for select Name.ID from Name, Name_Address where CO_ID = @co_id AND COMPANY_RECORD = 0 AND Name.ID = Name_Address.ID AND Name.ADDRESS_NUM_1 = Name.MAIL_ADDRESS_NUM and Name.MAIL_ADDRESS_NUM = Name_Address.ADDRESS_NUM
       open Get_Address_Children
       fetch next from Get_Address_Children into @childID
       while @@FETCH_STATUS = 0
       begin
         exec sp_asi_UpdateNameIndex @childID, @fieldName, @fieldValue, @indexName
         fetch next from Get_Address_Children into @childID
       end
       close Get_Address_Children
       deallocate Get_Address_Children
     end
     else
     begin
       declare Get_Children cursor for select ID from Name where CO_ID = @co_id AND COMPANY_RECORD = 0
       open Get_Children
       fetch next from Get_Children into @childID
       while @@FETCH_STATUS = 0
       begin
         exec sp_asi_UpdateNameIndex @childID, @fieldName, @fieldValue, @indexName
         fetch next from Get_Children into @childID
       end
       close Get_Children
       deallocate Get_Children
     end
   end

GO
GRANT EXECUTE ON  [dbo].[sp_asi_FlowdownNameIndex] TO [IMIS]
GO